Microsoft Excel (2013)

This first example is a simple report on single value fields from the POS module. The VBA code provided in this example will automatically populate headings and row data for each column selected.

In Vitalware:

1. Search for or otherwise list a group of records on which to report.

   In this example we search for records in the Loans module.

2. Click Reports in the Tool bar to display the Reports box.
3. Click New in the Reports box to display the Report Properties box.
4. On the Report Type tab, enter a descriptive name for the Report.
5. Select Microsoft ADO Report from the Type drop list:

   

6. On the Fields tab, add the fields you wish to include in the report.

   In this example we add:

   

7. Make changes to the Sort Order, Sort Options and Security tabs as required.

   Note: For full details of how to create the report in Vitalware, see 1: Create the report in Vitalware.

8. Click OK to add the new report to the Reports box:

   

9. Run the report:
   1. Select the report in the Reports box.
   2. Click Report All.

   A message displays indicating that your report does not exist on the server. This is to be expected as the report has not yet been built in the third party application (e.g. Excel):

   

10. Click Yes.

    An xml file is generated and saved with the data from your record set. The location of this file can vary, but typically it can be found in:

    C:\Users\[your username]\AppData\Local\KESoftware\Reports\e[module name]

    For example, a report run in the Parties module, will save the xmldata file to:

    C:\Users\[your username]\AppData\Local\KESoftware\Reports\eparties

    Note: If you click No, the output files will still be generated and copied to your computer.

    What happens next depends on the Reports|Extension Registry entry. For example, if a Reports|Extension Registry entry associates the Microsoft ADO report type with Excel files, Microsoft Excel will open.

    Tip: If you want Microsoft ADO reports to be associated with MS Word, edit the Reports|Extension Registry entry accordingly. Otherwise, simply close Excel, open MS Word instead and add your code to Word. All that matters is that you create a connection between the Microsoft report (whether it is Word, Excel, Access, etc.) and the xmldata file.

11. In this example we open a Blank Excel workbook.

1. On the Developer tab in Excel, click Visual Basic.

   Microsoft Visual Basic for Applications will open.

   Tip: If the Developer tab does not display, ensure that Excel has been set up correctly.

2. Double-click Sheet1 in the VBAProject pane.
3. Add your code.
   In this example we copy and paste the following VB code

   Sub OpenAdoFile()

   Dim RecordSet As ADODB.RecordSet

   Dim Worksheet As Excel.Worksheet

   Dim h As Long

   Dim col As Long

   Dim datarow As Long

   Dim source As String

    

   'Get the persisted record set. This example specifies epos

   ' Be sure to specify the correct table in your code

   source = Environ("LocalAppData") & "\KESoftware\Reports\epos\xmldata.xml"

   Set RecordSet = New ADODB.RecordSet

   RecordSet.Open source, "Provider=MSPersist"

    

   'Get the active page to send the data to

   Set Worksheet = ThisWorkbook.ActiveSheet

   Application.Visible = True

    

   'Put out all of the column headers

   col = 1

   ListColumnNames Worksheet, RecordSet, col

    

   'Print out all the row data

   While Not RecordSet.EOF

   col = 1

   datarow = datarow + 1

   For h = 0 To RecordSet.Fields.count - 1

   Worksheet.Cells(datarow + 1, col).Value = RecordSet.Fields(h).Value

   col = col + 1

   Next

   RecordSet.MoveNext

   Wend

    

   Worksheet.Range("A1").CurrentRegion.Select

   Worksheet.Columns.AutoFit

   Set RecordSet = Nothing

   End Sub

    

   Private Sub ListColumnNames(ByVal ws As Excel.Worksheet, ByVal rs As ADODB.RecordSet, ByRef col As Long)

   Dim i As Long

   'Loop through the record set pulling out the column names

   For i = 0 To rs.Fields.count - 1

   ws.Cells(1, col).Value = rs.Fields(i).Name

   col = col + 1

   Next

   End Sub

   

4. Double-click ThisWorbook in the VBAProject pane and add the following code:

   Sub Workbook_Open()

   'Load up the ADO File

   Sheet1.OpenAdoFile

   End Sub

   

5. Save the report as macro-enabled and upload it to your Vitalware report.
6. Run the report: